﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 马铃薯生产经营实体及相关情况通用操作类
    /// </summary>
    public class PotProsecutionServices: PotProsecutions
    {
        public Pager<List<PotProsecutionEntity>> GetPageList(PotProsecutionQuery query)
        {
            Pager<List<PotProsecutionEntity>> obj = new Pager<List<PotProsecutionEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_PotProsecution where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<PotProsecutionEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取马铃薯生产经营实体及相关情况分页列表失败", ex);
                return obj;
            }
        }
        public ResResult<ProsecutionFillModel> GetModel(string AreaCode, string FillInYear)
        {
            ReportServices reportService = new ReportServices();
            ResResult<ProsecutionFillModel> obj = new ResResult<ProsecutionFillModel>();
            ProsecutionFillModel model = new ProsecutionFillModel();
            int oldFillInYear = Convert.ToInt32(FillInYear) - 1;
            string strSql = "select t1.* from T_PotProsecution t1 join T_Report t2 on t1.ReportId = t2.Id";
            strSql += "  where t2.AreaCode = @AreaCode and t2.FillInYear = @FillInYear and ReportCode = 'PotProsecution' ";
            if (reportService.CheckRepCreate("PotProsecution", AreaCode, FillInYear))
            {
                obj.status = DefaultKey.ErrorResult;
                obj.model = model;
                obj.message = FillInYear + "年的全年生产情况表没有生成！";
            }
            else
            {
                ReportEntity reportModel = reportService.GetReportView("PotProsecution", AreaCode, FillInYear);
                model.ReportStatus = reportModel.Status;
                model.ReportId = reportModel.Id;
                model.Opinion = reportModel.Opinion;
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    if (reportService.CheckRepCount(model.ReportId, "T_PotProsecution"))
                    {
                        PotProsecutionEntity entity = new PotProsecutionEntity();
                        entity.ReportId = model.ReportId;
                        AddInfo(entity);
                    }
                    model.DataModel = dbCon.Query<PotProsecutionEntity>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    if (!reportService.CheckRepCreate("PotProsecution", AreaCode, oldFillInYear.ToString()))
                    {
                        model.OldDataModel = dbCon.Query<PotProsecutionEntity>(strSql, new { AreaCode = AreaCode, FillInYear = oldFillInYear.ToString() }).FirstOrDefault();
                    }
                }
                obj.model = model;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            return obj;
        }

        public PotProsecutionEntity ExcelToModel(string pathName, string fileSuffix)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            PotProsecutionEntity pageModel = new PotProsecutionEntity();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            sheet = workbook.GetSheetAt(0);

            if (sheet != null)
            {
                HSSFRow datarow1 = (HSSFRow)sheet.GetRow(4);
                //if (datarow1.GetCell(3) != null)
                //{
                //    if (datarow1.GetCell(3).CellType == CellType.Formula)
                //    {
                //        pageModel.MarketNum = (int)(datarow1.GetCell(3).NumericCellValue);
                //    }
                //    else
                //    {
                //        if (DataCheck.isNumber(datarow1.GetCell(3).ToString()))
                //        {
                //            pageModel.MarketNum = int.Parse(datarow1.GetCell(3).ToString());
                //        }

                //    }
                //}




                HSSFRow datarow2 = (HSSFRow)sheet.GetRow(5);
                if (datarow2.GetCell(3) != null)
                {
                    if (datarow2.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.CdxMarket = (int)(datarow2.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow2.GetCell(3).ToString()))
                        {
                            pageModel.CdxMarket = int.Parse(datarow2.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.CdxMarket = 0;
                }




                HSSFRow datarow3 = (HSSFRow)sheet.GetRow(6);
                if (datarow3.GetCell(3) != null)
                {
                    if (datarow3.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.ZypfMarket = (int)(datarow3.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow3.GetCell(3).ToString()))
                        {
                            pageModel.ZypfMarket = int.Parse(datarow3.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.ZypfMarket = 0;
                }




                HSSFRow datarow4 = (HSSFRow)sheet.GetRow(7);
                if (datarow4.GetCell(3) != null)
                {
                    if (datarow4.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.QyxzhpfMarket = (int)(datarow4.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow4.GetCell(3).ToString()))
                        {
                            pageModel.QyxzhpfMarket = int.Parse(datarow4.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.QyxzhpfMarket = 0;
                }

                int markets = 0;
                if(pageModel.CdxMarket != null)
                {
                    markets += (int)pageModel.CdxMarket;
                }
                if (pageModel.ZypfMarket != null)
                {
                    markets += (int)pageModel.ZypfMarket;
                }
                if (pageModel.QyxzhpfMarket != null)
                {
                    markets += (int)pageModel.QyxzhpfMarket;
                }
                pageModel.MarketNum = markets;

                HSSFRow datarow5 = (HSSFRow)sheet.GetRow(8);
                if (datarow5.GetCell(3) != null)
                {
                    if (datarow5.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.FruCompanyNum = (int)(datarow5.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow5.GetCell(3).ToString()))
                        {
                            pageModel.FruCompanyNum = int.Parse(datarow5.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.FruCompanyNum = 0;
                }




                HSSFRow datarow6 = (HSSFRow)sheet.GetRow(9);
                if (datarow6.GetCell(3) != null)
                {
                    if (datarow6.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.ZzNum_country = (int)(datarow6.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow6.GetCell(3).ToString()))
                        {
                            pageModel.ZzNum_country = int.Parse(datarow6.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.ZzNum_country = 0;
                }




                HSSFRow datarow7 = (HSSFRow)sheet.GetRow(10);
                if (datarow7.GetCell(3) != null)
                {
                    if (datarow7.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.ZzNum_pro = (int)(datarow7.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow7.GetCell(3).ToString()))
                        {
                            pageModel.ZzNum_pro = int.Parse(datarow7.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.ZzNum_pro = 0;
                }




                HSSFRow datarow8 = (HSSFRow)sheet.GetRow(11);
                if (datarow8.GetCell(3) != null)
                {
                    if (datarow8.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.ZzNum_city = (int)(datarow8.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow8.GetCell(3).ToString()))
                        {
                            pageModel.ZzNum_city = int.Parse(datarow8.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.ZzNum_city = 0;
                }




                HSSFRow datarow9 = (HSSFRow)sheet.GetRow(12);
                if (datarow9.GetCell(3) != null)
                {
                    if (datarow9.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.ZzNum_county = (int)(datarow9.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow9.GetCell(3).ToString()))
                        {
                            pageModel.ZzNum_county = int.Parse(datarow9.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.ZzNum_county = 0;
                }




                HSSFRow datarow10 = (HSSFRow)sheet.GetRow(13);
                if (datarow10.GetCell(3) != null)
                {
                    if (datarow10.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.ZzNum_other = (int)(datarow10.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow10.GetCell(3).ToString()))
                        {
                            pageModel.ZzNum_other = int.Parse(datarow10.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.ZzNum_other = 0;
                }
                int companys = 0;
                if (pageModel.ZzNum_other != null)
                {
                    companys += (int)pageModel.ZzNum_other;
                }
                if (pageModel.ZzNum_county != null)
                {
                    companys += (int)pageModel.ZzNum_county;
                }
                if (pageModel.ZzNum_city != null)
                {
                    companys += (int)pageModel.ZzNum_city;
                }
                if (pageModel.ZzNum_pro != null)
                {
                    companys += (int)pageModel.ZzNum_pro;
                }
                if (pageModel.ZzNum_country != null)
                {
                    companys += (int)pageModel.ZzNum_country;
                }
                pageModel.FruCompanyNum = companys;


                HSSFRow datarow11 = (HSSFRow)sheet.GetRow(15);
                if (datarow11.GetCell(3) != null)
                {
                    if (datarow11.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.HzNum = (int)(datarow11.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow11.GetCell(3).ToString()))
                        {
                            pageModel.HzNum = int.Parse(datarow11.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.HzNum = 0;
                }




                HSSFRow datarow12 = (HSSFRow)sheet.GetRow(16);
                if (datarow12.GetCell(3) != null)
                {
                    if (datarow12.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.HznhNum = (int)(datarow12.GetCell(3).NumericCellValue);
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow12.GetCell(3).ToString()))
                        {
                            pageModel.HznhNum = int.Parse(datarow12.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.HznhNum = 0;
                }




                HSSFRow datarow13 = (HSSFRow)sheet.GetRow(17);
                if (datarow13.GetCell(3) != null)
                {
                    if (datarow13.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.NmhzArea = decimal.Parse(datarow13.GetCell(3).NumericCellValue.ToString());
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow13.GetCell(3).ToString()))
                        {
                            pageModel.NmhzArea = decimal.Parse(datarow13.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.NmhzArea = 0;
                }




                HSSFRow datarow30 = (HSSFRow)sheet.GetRow(18);
                if (datarow30.GetCell(3) != null)
                {
                    if (datarow30.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.HzlzArea = decimal.Parse(datarow30.GetCell(3).NumericCellValue.ToString());
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow30.GetCell(3).ToString()))
                        {
                            pageModel.HzlzArea = decimal.Parse(datarow30.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.HzlzArea = 0;
                }




                HSSFRow datarow14 = (HSSFRow)sheet.GetRow(19);
                if (datarow14.GetCell(3) != null)
                {
                    if (datarow14.GetCell(3).CellType == CellType.Formula)
                    {
                        pageModel.NmhzYincome = decimal.Parse(datarow14.GetCell(3).NumericCellValue.ToString());
                    }
                    else
                    {
                        if (DataCheck.isNumber(datarow14.GetCell(3).ToString()))
                        {
                            pageModel.NmhzYincome = decimal.Parse(datarow14.GetCell(3).ToString());
                        }

                    }
                }
                else
                {
                    pageModel.NmhzYincome = 0;
                }
               

            }
            return pageModel;
        }

        public ResResult<List<ProsecutionStatisModel>> getStatisListData(string AreaCode, string FillInYear)
        {
            ResResult<List<ProsecutionStatisModel>> obj = new ResResult<List<ProsecutionStatisModel>>();
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                list = getProvinStatisList(FillInYear);
                //var citylist = areaServices.GetPrefectureList();
                //ProsecutionStatisModel totalModel = new ProsecutionStatisModel();
                //totalModel.AreaCode = AreaCode;
                //totalModel.AreaName = "合计";
                //totalModel.tag = 2;
                //totalModel.MarketNum = 0;
                //totalModel.CdxMarket = 0;
                //totalModel.ZypfMarket = 0;
                //totalModel.QyxzhpfMarket = 0;
                //totalModel.FruCompanyNum = 0;
                //totalModel.ZzNum_country = 0;
                //totalModel.ZzNum_pro = 0;
                //totalModel.ZzNum_city = 0;
                //totalModel.ZzNum_county = 0;
                //totalModel.ZzNum_other = 0;
                //totalModel.HzNum = 0;
                //totalModel.HznhNum = 0;
                //totalModel.NmhzArea = 0;
                //totalModel.HzlzArea = 0;
                //totalModel.NmhzYincome = 0;
                //foreach (string code in citylist)
                //{
                //    ProsecutionStatisModel model = getProvinStatisList(code, FillInYear);
                //    if (model != null)
                //    {
                //        //int len = templist.Count;
                //        //templist[len - 1].AreaName = areaServices.GetByCodeModel(code).model.Name;
                //        // templist[len - 1].tag = 1;   
                //        model.AreaName = areaServices.GetByCodeModel(code).model.Name;
                //        totalModel.MarketNum += model.MarketNum;
                //        totalModel.CdxMarket += model.CdxMarket;
                //        totalModel.ZypfMarket += model.ZypfMarket;
                //        totalModel.QyxzhpfMarket += model.QyxzhpfMarket;
                //        totalModel.FruCompanyNum += model.FruCompanyNum;
                //        totalModel.ZzNum_country += model.ZzNum_country;
                //        totalModel.ZzNum_pro += model.ZzNum_pro;
                //        totalModel.ZzNum_city += model.ZzNum_city;
                //        totalModel.ZzNum_county += model.ZzNum_county;
                //        totalModel.ZzNum_other += model.ZzNum_other;
                //        totalModel.HzNum += model.HzNum;
                //        totalModel.HznhNum += model.HznhNum;
                //        totalModel.NmhzArea += model.NmhzArea;
                //        totalModel.HzlzArea += model.HzlzArea;
                //        totalModel.NmhzYincome += model.NmhzYincome;
                //        list.Add(model);
                //    }
                //}
                //list.Add(totalModel);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getAreaStatisList(AreaCode, FillInYear);
                    int len = list.Count;
                    // list[len - 1].tag = 1;
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        public List<ProsecutionStatisModel> getProvinStatisList(string FillInYear)
        {
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            string strSql = "select ta.FullName AreaName ,tt.* from ( ";
            strSql += " select case when grouping(t1.ParentCode)=1 then '530000000000' else ParentCode end AreaCode,";
            strSql += "isnull(sum(t3.marketNum),0.00) MarketNum ,";
            strSql += "isnull(sum(t3.cdxMarket),0.00) CdxMarket ,";
            strSql += "isnull(sum(t3.zypfMarket),0.00) ZypfMarket ,";
            strSql += "isnull(sum(t3.qyxzhpfMarket),0.00) QyxzhpfMarket ,";
            strSql += "isnull(sum(t3.FruCompanyNum),0.00) FruCompanyNum,";
            strSql += "isnull(sum(t3.zzNum_country),0.00) ZzNum_country,";
            strSql += "isnull(sum(t3.zzNum_pro),0.00) ZzNum_pro,";
            strSql += "isnull(sum(t3.zzNum_city),0.00) ZzNum_city ,";
            strSql += "isnull(sum(t3.zzNum_county),0.00) ZzNum_county ,";
            strSql += "isnull(sum(t3.zzNum_other),0.00) ZzNum_other ,";
            strSql += "isnull(sum(t3.HzNum),0.00) HzNum ,";
            strSql += "isnull(sum(t3.HznhNum),0.00) HznhNum ,";
            strSql += "isnull(sum(t3.NmhzArea),0.00) NmhzArea ,";
            strSql += "isnull(sum(t3.hzlzArea),0.00) HzlzArea ,";
            strSql += "isnull(sum(t3.nmhzYincome),0.00) NmhzYincome ";
            strSql += " from T_Area t1 left join";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += " left join  T_PotProsecution t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotProsecution'  and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            strSql += " and (t1.ParentCode != '530000000000' or t1.Code != '530000000000')";
            strSql += " group by rollup(t1.ParentCode)";
            strSql += " ) tt left join T_Area ta on tt.AreaCode = ta.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProsecutionStatisModel>(strSql, new { FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯经营实体情况调查统计列表信息失败", ex);
            }
            return list;
        }
        public List<ProsecutionStatisModel> getAreaStatisList(string AreaCode, string FillInYear)
        {
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            string strSql = "select t.FullName AreaName,tt.* from (";
            strSql += "select case when grouping(t1.Code)=1 then '" + AreaCode + "' else Code end AreaCode,";
            strSql += "isnull(sum(t3.marketNum),0.00) MarketNum ,";
            strSql += "isnull(sum(t3.cdxMarket),0.00) CdxMarket ,";
            strSql += "isnull(sum(t3.zypfMarket),0.00) ZypfMarket ,";
            strSql += "isnull(sum(t3.qyxzhpfMarket),0.00) QyxzhpfMarket ,";
            strSql += "isnull(sum(t3.FruCompanyNum),0.00) FruCompanyNum,";
            strSql += "isnull(sum(t3.zzNum_country),0.00) ZzNum_country,";
            strSql += "isnull(sum(t3.zzNum_pro),0.00) ZzNum_pro,";
            strSql += "isnull(sum(t3.zzNum_city),0.00) ZzNum_city ,";
            strSql += "isnull(sum(t3.zzNum_county),0.00) ZzNum_county ,";
            strSql += "isnull(sum(t3.zzNum_other),0.00) ZzNum_other ,";
            strSql += "isnull(sum(t3.HzNum),0.00) HzNum ,";
            strSql += "isnull(sum(t3.HznhNum),0.00) HznhNum ,";
            strSql += "isnull(sum(t3.NmhzArea),0.00) NmhzArea ,";
            strSql += "isnull(sum(t3.hzlzArea),0.00) HzlzArea ,";
            strSql += "isnull(sum(t3.nmhzYincome),0.00) NmhzYincome ";
            strSql += " from T_Area t1 left join";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_PotProsecution t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotProsecution' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            strSql += "group by rollup(t1.Code)";
            strSql += " )tt inner join  T_Area t on t.Code = tt.AreaCode order by t.Code desc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProsecutionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯经营实体情况调查统计列表信息失败", ex);
            }
            return list;
        }

        public List<ProsecutionStatisModel> getCountyStatisList(string AreaCode, string FillInYear)
        {
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            string strSql = "select t1.FullName AreaName,";
            strSql += "isnull(t3.marketNum,0.00) MarketNum ,";
            strSql += "isnull(t3.cdxMarket,0.00) CdxMarket ,";
            strSql += "isnull(t3.zypfMarket,0.00) ZypfMarket ,";
            strSql += "isnull(t3.qyxzhpfMarket,0.00) QyxzhpfMarket ,";
            strSql += "isnull(t3.FruCompanyNum,0.00) FruCompanyNum,";
            strSql += "isnull(t3.zzNum_country,0.00) ZzNum_country,";
            strSql += "isnull(t3.zzNum_pro,0.00) ZzNum_pro,";
            strSql += "isnull(t3.zzNum_city,0.00) ZzNum_city ,";
            strSql += "isnull(t3.zzNum_county,0.00) ZzNum_county ,";
            strSql += "isnull(t3.zzNum_other,0.00) ZzNum_other ,";
            strSql += "isnull(t3.HzNum,0.00) HzNum ,";
            strSql += "isnull(t3.HznhNum,0.00) HznhNum ,";
            strSql += "isnull(t3.NmhzArea,0.00) NmhzArea ,";
            strSql += "isnull(t3.hzlzArea,0.00) HzlzArea ,";
            strSql += "isnull(t3.nmhzYincome,0.00) NmhzYincome ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += " left join  T_PotProsecution t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotProsecution' and t1.Code = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProsecutionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯经营实体情况调查统计列表信息失败", ex);
            }
            return list;
        }

        public ResResult<List<List<AllYearStatisModel>>> GetAllYearStatisList(string AreaCode)
        {
            ResResult<List<List<AllYearStatisModel>>> obj = new ResResult<List<List<AllYearStatisModel>>>();

            List<List<AllYearStatisModel>> list = new List<List<AllYearStatisModel>>();
            AreaServices areaservices = new AreaServices();
            DictServices dictService = new DictServices();
            var yearList = dictService.GetYearList();
            var childAreaList = areaservices.getChildAreaCode(AreaCode);
            if (AreaCode.Equals("530000000000"))
            {
                foreach (var item in childAreaList)
                {
                    var statislist = GetAllYearCityData(item.Code);
                    var datalist = ParseData(yearList, statislist);
                    List<AllYearStatisModel> templist = new List<AllYearStatisModel>();
                    for (int i = 0; i < datalist.Count; i++)
                    {
                        var dataItem = datalist[i];
                        AllYearStatisModel model = new AllYearStatisModel();
                        model.AreaName = item.Name;
                        model.allYearDataList = dataItem;
                        templist.Add(model);
                    }
                    list.Add(templist);
                }
                var totalstatislist = GetAllYearProvinData();
                var totaldatalist = ParseData(yearList, totalstatislist);
                List<AllYearStatisModel> totaltemplist = new List<AllYearStatisModel>();
                for (int i = 0; i < totaldatalist.Count; i++)
                {
                    var dataItem = totaldatalist[i];
                    AllYearStatisModel model = new AllYearStatisModel();
                    model.AreaName = "全省";
                    model.allYearDataList = dataItem;
                    totaltemplist.Add(model);
                }
                list.Add(totaltemplist);
            }
            else if (AreaCode.Substring(4, 8).Equals("00000000"))
            {
                foreach (var item in childAreaList)
                {
                    var statislist = GetAllYearCountyData(item.Code);
                    var datalist = ParseData(yearList, statislist);
                    List<AllYearStatisModel> templist = new List<AllYearStatisModel>();
                    for (int i = 0; i < datalist.Count; i++)
                    {
                        var dataItem = datalist[i];
                        AllYearStatisModel model = new AllYearStatisModel();
                        model.AreaName = item.Name;
                        model.allYearDataList = dataItem;
                        templist.Add(model);
                    }
                    list.Add(templist);
                }
                var totalstatislist = GetAllYearCityData(AreaCode);
                var totaldatalist = ParseData(yearList, totalstatislist);
                List<AllYearStatisModel> totaltemplist = new List<AllYearStatisModel>();
                for (int i = 0; i < totaldatalist.Count; i++)
                {
                    var dataItem = totaldatalist[i];
                    AllYearStatisModel model = new AllYearStatisModel();
                    model.AreaName = "全州（市）";
                    model.allYearDataList = dataItem;
                    totaltemplist.Add(model);
                }
                list.Add(totaltemplist);
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        /// <summary>
        /// 获取区县级所有年份统计数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        public List<ProsecutionStatisModel> GetAllYearCountyData(string AreaCode)
        {
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            string strSql = "select t1.Name AreaName,t4.DictValue FillInYear,t3.* ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join ";
            strSql += " T_PotProsecution t3 on t2.Id = t3.ReportId right join T_Dict t4 on t2.FillInYear = t4.DictValue";
            strSql += " where t2.ReportCode = 'PotProsecution' and t2.AreaCode = @AreaCode ";
            strSql += " and GroupCode='ReportYear' and t4.DictText !='全部' ";
            strSql += "  and t2.status=  '4' ";
            strSql += " order by t4.DictValue asc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProsecutionStatisModel>(strSql, new { AreaCode = AreaCode }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        /// <summary>
        /// 分解属性成各个列表，共15个前端所需显示指标列表
        /// </summary>
        /// <param name="YearList"></param>
        /// <param name="DataList"></param>
        /// <returns></returns>
        public List<List<decimal?>> ParseData(List<string> YearList, List<ProsecutionStatisModel> DataList)
        {
            List<List<decimal?>> list = new List<List<decimal?>>();
            List<decimal?> list1 = new List<decimal?>(),
                list2 = new List<decimal?>(), list3 = new List<decimal?>(),
                list4 = new List<decimal?>(), list5 = new List<decimal?>(),
                list6 = new List<decimal?>(), list7 = new List<decimal?>(),
                list8 = new List<decimal?>(), list9 = new List<decimal?>(),
                list10 = new List<decimal?>(), list11 = new List<decimal?>(),
                list12 = new List<decimal?>(), list13 = new List<decimal?>(),
                list14 = new List<decimal?>(), list15 = new List<decimal?>();

            foreach (var year in YearList)
            {
                var preYear = (int.Parse(year) - 1).ToString();
                decimal? temp1 = 0, temp2 = 0, temp3 = 0, temp4 = 0, temp5 = 0, temp6 = 0,
                temp7 = 0, temp8 = 0, temp9 = 0, temp10 = 0, temp11 = 0, temp12 = 0, temp13 = 0,
                temp14 = 0, temp15 = 0;
                if (DataList != null && DataList.Count > 0)
                {
                    for (int i = 0; i < DataList.Count; i++)
                    {
                        var Item = DataList[i];
                        if (Item.FillInYear.Equals(year))
                        {
                            temp1 = Item.MarketNum;
                            temp2 = Item.CdxMarket;
                            temp3 = Item.ZypfMarket;
                            temp4 = Item.QyxzhpfMarket;
                            temp5 = Item.FruCompanyNum;
                            temp6 = Item.ZzNum_country;
                            temp7 = Item.ZzNum_pro;
                            temp8 = Item.ZzNum_city;
                            temp9 = Item.ZzNum_county;
                            temp10 = Item.ZzNum_other;
                            temp11 = Item.HzNum;

                            temp12 = Item.HznhNum;
                            temp13 = Item.NmhzArea;
                            temp14 = Item.HzlzArea;
                            temp15 = Item.NmhzYincome;
                        }
                    }
                   
                }
                list1.Add(temp1); list2.Add(temp2); list3.Add(temp3); list4.Add(temp4); list5.Add(temp5); list6.Add(temp6);
                list7.Add(temp7); list8.Add(temp8); list9.Add(temp9); list10.Add(temp10); list11.Add(temp11); list12.Add(temp12);
                list13.Add(temp13); list14.Add(temp14); list15.Add(temp15); 
            }
            //按顺序加入list
            list.Add(list1); list.Add(list2); list.Add(list3); list.Add(list4); list.Add(list5); list.Add(list6); list.Add(list7);
            list.Add(list8); list.Add(list9); list.Add(list10); list.Add(list11); list.Add(list12); list.Add(list13); list.Add(list14);
            list.Add(list15); 

            return list;
        }

        /// <summary>
        /// 获取州市级所有年份统计数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        public List<ProsecutionStatisModel> GetAllYearCityData(string AreaCode)
        {
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            string strSql = "select t4.DictValue FillInYear, ";
            strSql += " isnull(sum(t3.MarketNum),0.00) MarketNum, ";
            strSql += " isnull(sum(t3.CdxMarket),0.00) CdxMarket, ";
            strSql += " isnull(sum(t3.ZypfMarket),0.00) ZypfMarket, ";
            strSql += " isnull(sum(t3.QyxzhpfMarket),0.00) QyxzhpfMarket,";
            strSql += " isnull(sum(t3.FruCompanyNum),0.00) FruCompanyNum, ";
            strSql += " isnull(sum(t3.ZzNum_country),0.00) ZzNum_country, ";
            strSql += " isnull(sum(t3.ZzNum_pro),0.00) ZzNum_pro, ";
            strSql += " isnull(sum(t3.ZzNum_city),0.00) ZzNum_city, ";
            strSql += " isnull(sum(t3.ZzNum_county),0.00) ZzNum_county, ";
            strSql += " isnull(sum(t3.ZzNum_other),0.00) ZzNum_other,  ";
            strSql += " isnull(sum(t3.HzNum),0.00) HzNum, ";
            strSql += " isnull(sum(t3.HznhNum),0.00) HznhNum, ";
            strSql += " isnull(sum(t3.NmhzArea),0.00) NmhzArea, ";
            strSql += " isnull(sum(t3.HzlzArea),0.00) HzlzArea, ";
            strSql += " isnull(sum(t3.NmhzYincome),0.00) NmhzYincome ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotProsecution t3 on t2.Id = t3.ReportId right join T_Dict t4 on t2.FillInYear = t4.DictValue ";
            strSql += "  where t2.ReportCode = 'PotProsecution' and t1.ParentCode =@AreaCode ";
            strSql += " and GroupCode='ReportYear' and t4.DictText !='全部' ";
            strSql += "  and t2.status=  '4' ";

            strSql += " group by t4.DictValue  order by t4.DictValue asc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProsecutionStatisModel>(strSql, new { AreaCode = AreaCode }).ToList();
                    dbCon.Dispose();//数据库连接释放

                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }

        /// <summary>
        /// 获取全省所有年度统计数据
        /// </summary>
        /// <returns></returns>
        public List<ProsecutionStatisModel> GetAllYearProvinData()
        {
            List<ProsecutionStatisModel> list = new List<ProsecutionStatisModel>();
            string strSql = "select t4.DictValue FillInYear, ";
            strSql += " isnull(sum(t3.MarketNum),0.00) MarketNum, ";
            strSql += " isnull(sum(t3.CdxMarket),0.00) CdxMarket, ";
            strSql += " isnull(sum(t3.ZypfMarket),0.00) ZypfMarket, ";
            strSql += " isnull(sum(t3.QyxzhpfMarket),0.00) QyxzhpfMarket,";
            strSql += " isnull(sum(t3.FruCompanyNum),0.00) FruCompanyNum, ";
            strSql += " isnull(sum(t3.ZzNum_country),0.00) ZzNum_country, ";
            strSql += " isnull(sum(t3.ZzNum_pro),0.00) ZzNum_pro, ";
            strSql += " isnull(sum(t3.ZzNum_city),0.00) ZzNum_city, ";
            strSql += " isnull(sum(t3.ZzNum_county),0.00) ZzNum_county, ";
            strSql += " isnull(sum(t3.ZzNum_other),0.00) ZzNum_other,  ";
            strSql += " isnull(sum(t3.HzNum),0.00) HzNum, ";
            strSql += " isnull(sum(t3.HznhNum),0.00) HznhNum, ";
            strSql += " isnull(sum(t3.NmhzArea),0.00) NmhzArea, ";
            strSql += " isnull(sum(t3.HzlzArea),0.00) HzlzArea, ";
            strSql += " isnull(sum(t3.NmhzYincome),0.00) NmhzYincome ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotProsecution t3 on t2.Id = t3.ReportId right join T_Dict t4 on t2.FillInYear = t4.DictValue ";
            strSql += "  where t2.ReportCode = 'PotProsecution' and t1.ParentCode !='530000000000'  and t1.ParentCode is not null ";
            strSql += " and GroupCode='ReportYear' and t4.DictText !='全部' ";
            strSql += "  and t2.status=  '4' ";

            strSql += " group by t4.DictValue  order by t4.DictValue asc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProsecutionStatisModel>(strSql).ToList();
                    dbCon.Dispose();//数据库连接释放


                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }


    }
}